﻿Public Class ClienteDAO
    Dim objAcessoDados As AcessoDados = New AcessoDados(Config.StringConexao)

    Public Function ListaTodos() As List(Of Cliente)
        Dim registros As DataSet = New DataSet
        registros = objAcessoDados.ExecutaSqlRetorno("select * from tb_cliente")
        Return CriarListaCli(registros)
    End Function

    Public Function CriarListaCli(ByVal Registros As DataSet) As List(Of Cliente)
        Dim lista As New List(Of Cliente)
        Dim objCli As Cliente
        For Each dr As DataRow In Registros.Tables(0).Rows
            objCli = CriarCli(dr)
            lista.Add(objCli)
        Next
        Return lista
    End Function
    Public Function CriarCli(ByVal row As DataRow) As Cliente
        Dim objCli As New Cliente
        Dim objCid As New Cidade
        Dim objDesp As New Despachante
        Dim objCidDao As New CidadeDAO
        Dim objDespDao As New DespachateDAO

        objCli.CodCli = CInt(row.Item("cod_cli"))
        objCli.NomeCli = CStr(row.Item("nome_cli"))
        objCli.EmailCli = CStr(row.Item("email_cli"))
        objCli.TelCli = CStr(row.Item("tel_cli"))
        objCli.EndCli = CStr(row.Item("end_cli"))
        objCli.CpfCnpjCli = CStr(row.Item("cpf_cnpj_cli"))
        objCli.Despachante = objDespDao.SelectDespId(CInt(row.Item("fk_cod_desp")))
        objCli.Cidade = objCidDao.SelectCidadeId(CInt(row.Item("fk_cod_cid")))

        Return objCli
    End Function

    Public Function Inserir(ByVal iCli As Cliente) As Integer
        Dim registros As Integer
        Dim sql As New StringBuilder
        sql.Append("insert into tb_cliente (")
        sql.Append(" nome_cli, ")
        sql.Append(" email_cli, ")
        sql.Append(" tel_cli, ")
        sql.Append(" end_cli, ")
        sql.Append(" cpf_cnpj_cli, ")
        sql.Append(" fk_cod_desp, ")
        sql.Append(" fk_cod_cid ")
        sql.Append(") values (")
        sql.Append("'" & iCli.NomeCli.ToString & "',")
        sql.Append("'" & iCli.EmailCli.ToString & "',")
        sql.Append("'" & iCli.TelCli.ToString & "',")
        sql.Append("'" & iCli.EndCli.ToString & "',")
        sql.Append("'" & iCli.CpfCnpjCli.ToString & "',")
        sql.Append("" & iCli.Despachante.CodDesp & ",")
        sql.Append("" & iCli.Cidade.CodCid & ")")
        registros = objAcessoDados.ExecutaSql(sql.ToString)
        Return registros
    End Function

    Public Function SelectCliente(ByVal cod_desp As Integer) As Cliente
        Dim objCli As Cliente
        Dim registro As DataSet = New DataSet

        Dim sql As New StringBuilder
        Dim strWhere As String = " Where "
        sql.Append("select * from tb_cliente ")
        sql.Append(strWhere & "fk_cod_desp = " & cod_desp)
        registro = objAcessoDados.ExecutaSqlRetorno(sql.ToString)
        Try
            Dim objCliData As DataRow = registro.Tables(0).Rows(0)
            objCli = CriarCli(objCliData)
            Return objCli
        Catch ex As Exception
            Return Nothing
        End Try
        Return Nothing
    End Function

    Public Function Atualizar(ByVal cli As Cliente) As Integer
        Dim registros As Integer
        Dim sql As New StringBuilder
        sql.Append("update tb_cliente set ")
        sql.Append("nome_cli = '" & cli.NomeCli.ToString & "', ")
        sql.Append("email_cli = '" & cli.EmailCli.ToString & "', ")
        sql.Append("tel_cli = '" & cli.TelCli.ToString & "',")
        sql.Append("end_cli = '" & cli.EndCli.ToString & "',")
        sql.Append("cpf_cnpj_cli = '" & cli.CpfCnpjCli.ToString & "',")
        sql.Append("fk_cod_cid = " & cli.Cidade.CodCid)
        sql.Append("where cod_cli = " & cli.CodCli)
        registros = objAcessoDados.ExecutaSql(sql.ToString)
        Return registros
    End Function

    Public Function SelectCliId(ByVal cod_cli As Integer) As Cliente
        Dim objCli As Cliente
        Dim registro As DataSet = New DataSet

        Dim sql As New StringBuilder
        Dim strWhere As String = " Where "
        sql.Append("select * from tb_cliente ")
        sql.Append(strWhere & "cod_cli = " & cod_cli)
        registro = objAcessoDados.ExecutaSqlRetorno(sql.ToString)
        Try
            Dim objCliData As DataRow = registro.Tables(0).Rows(0)
            objCli = CriarCli(objCliData)
            Return objCli
        Catch ex As Exception
            Return Nothing
        End Try
        Return Nothing
    End Function

    Public Function SelectCliNome(ByVal nome_cli As String) As List(Of Cliente)
        Dim objCli As New List(Of Cliente)
        Dim registros As DataSet = New DataSet

        Dim sql As New StringBuilder
        Dim strWhere As String = " Where "
        sql.Append("select * from tb_cliente ")
        sql.Append(strWhere & "nome_cli like  '" & nome_cli.ToString & "%'")
        registros = objAcessoDados.ExecutaSqlRetorno(sql.ToString)
        Try
            Return CriarListaCli(registros)
        Catch ex As Exception
            Return Nothing
        End Try
        Return Nothing
    End Function

    Public Function SelectCliCpf(ByVal cpf_cnpj_cli As String) As Cliente
        Dim objCli As Cliente
        Dim registro As DataSet = New DataSet

        Dim sql As New StringBuilder
        Dim strWhere As String = " Where "
        sql.Append("select * from tb_cliente ")
        sql.Append(strWhere & "cpf_cnpj_cli = '" & cpf_cnpj_cli.ToString & "'")
        registro = objAcessoDados.ExecutaSqlRetorno(sql.ToString)
        Try
            Dim objCliData As DataRow = registro.Tables(0).Rows(0)
            objCli = CriarCli(objCliData)
            Return objCli
        Catch ex As Exception
            Return Nothing
        End Try
        Return Nothing
    End Function

    Public Function clienteJoinTodos(desp As Despachante) As DataTable
        Dim registros As DataTable = New DataTable
        Dim tables As New DataTable
        registros = objAcessoDados.ExecutaSqlRetornoDataTable(" SELECT c.cod_cli, c.NOME_CLI, c.EMAIL_CLI, c.TEL_CLI, c.END_CLI, c.CPF_CNPJ_CLI, e.NOME_CID, es.NOME_EST FROM TB_CLIENTE c, TB_CIDADE e, TB_ESTADO ES WHERE (c.fk_cod_cid = e.COD_CID) AND ( e.fk_cod_est = es.cod_est ) and (c.fk_cod_desp = " & desp.CodDesp & ")")
        Return registros
    End Function

    Public Function clienteJoinNome(ByVal cliente As Cliente, ByVal desp As Despachante) As DataTable
        Dim registros As DataTable = New DataTable
        Dim tables As New DataTable
        Dim sql As String = " SELECT c.cod_cli, c.NOME_CLI, c.EMAIL_CLI, c.TEL_CLI, c.END_CLI, c.CPF_CNPJ_CLI, e.NOME_CID, es.NOME_EST FROM TB_CLIENTE c, TB_CIDADE e, TB_ESTADO ES WHERE (c.nome_cli like '%" & cliente.NomeCli & "%') AND (c.fk_cod_cid = e.COD_CID) AND ( e.fk_cod_est = es.cod_est ) and (c.fk_cod_desp = " & desp.CodDesp & ")"
        Try
            registros = objAcessoDados.ExecutaSqlRetornoDataTable(sql)
            Return registros
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Function clienteJoinCodigo(ByVal cliente As Cliente, ByVal desp As Despachante) As DataTable
        Dim registros As DataTable = New DataTable
        Dim tables As New DataTable
        Dim sql As String = " SELECT c.cod_cli, c.NOME_CLI, c.EMAIL_CLI, c.TEL_CLI, c.END_CLI, c.CPF_CNPJ_CLI, e.NOME_CID, es.NOME_EST FROM TB_CLIENTE c, TB_CIDADE e, TB_ESTADO ES WHERE (c.cod_cli = " & cliente.CodCli & ") AND (c.fk_cod_cid = e.COD_CID) AND ( e.fk_cod_est = es.cod_est ) and (c.fk_cod_desp = " & desp.CodDesp & ")"
        Try
            registros = objAcessoDados.ExecutaSqlRetornoDataTable(sql)
            Return registros
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Function clienteJoinCpf(ByVal cliente As Cliente, ByVal desp As Despachante) As DataTable
        Dim registros As DataTable = New DataTable
        Dim tables As New DataTable
        Dim sql As String = " SELECT c.cod_cli, c.NOME_CLI, c.EMAIL_CLI, c.TEL_CLI, c.END_CLI, c.CPF_CNPJ_CLI, e.NOME_CID, es.NOME_EST FROM TB_CLIENTE c, TB_CIDADE e, TB_ESTADO ES WHERE (c.CPF_CNPJ_CLI = '" & cliente.CpfCnpjCli & "') AND (c.fk_cod_cid = e.COD_CID) AND ( e.fk_cod_est = es.cod_est ) and (c.fk_cod_desp = " & desp.CodDesp & ")"
        Try
            registros = objAcessoDados.ExecutaSqlRetornoDataTable(sql)
            Return registros
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

End Class
